package cj.web.activity.dao;
/*
 *  
 *  
*/
import java.util.List;
import net.paoding.rose.jade.annotation.DAO;
import net.paoding.rose.jade.annotation.SQL;
import net.paoding.rose.jade.annotation.SQLParam;
import cj.web.activity.domain.Generalize;
import java.util.Map;

/**
 * 
 * @author 
 *
 */
@DAO
public interface GeneralizeDAO{
	
	@SQL("SELECT * FROM generalize where ref_date>=CONVERT(varchar(100), :1, 23) and"
			+ " ref_date<CONVERT(varchar(100), :2, 23)")
    List<Generalize> queryAll(String startTime,String endTime);
	
	@SQL("SELECT count(1) FROM generalize where ref_date>=CONVERT(varchar(100), :1, 23) and"
			+ " ref_date<CONVERT(varchar(100), :2, 23) and mark=:3")
    int queryByMark(String startTime,String endTime,String mark);
    
    @SQL("select * from generalize where id=:1")
    Generalize queryById(String id);
    
    @SQL("INSERT INTO generalize ("
    		        		+ "id,"
			        		+ "ref_date,"
			        		+ "mark,"
			        		+ "device_type"
							+") VALUES ("
							+ ":t.id,"
							+ "CONVERT(varchar(100), GETDATE(), 21),"
							+ ":t.mark,"
							+ ":t.deviceType"
					+")")
    void create(@SQLParam("t") Generalize generalize);
	
	@SQL("UPDATE generalize SET "
	        + "ref_date=:t.refDate,"
	        + "mark=:t.mark,"
	        + "device_type=:t.deviceType"
			+ " WHERE id=:t.id")
    void update(@SQLParam("t") Generalize generalize);
	
	@SQL("Select COUNT(*) as appCount from generalize where mark='xctg'")
	int queryAPPDownloadNum();
	
	@SQL("SELECT COUNT(*) as number from ActiveManagement where ActiveID='5'")
	int queryNumber();
	
	@SQL("SELECT COUNT(*) as WinningNumber from ActiveManagement where ActiveID='5' and Remark <>'-1' and Status<>2")
	int queryWinningNumber();
	
	@SQL("select a.*,ifnull(b.CurrentNum,0) as CurrentNum,ifnull(c.TotalNum,0) as TotalNum from ("
			+"select id,name,count,remark,day_count as dayCount from active_gift_probability) as a left join "
			+"(SELECT Remark, COUNT(*) as CurrentNum from ActiveManagement where ActiveID='5' "
			+"and date_format(CreateTime,'%Y-%m-%d %H:%i:%s') = date_format(now(),'%Y-%m-%d %H:%i:%s') "
			+"and Remark <>'-1' and Status<>2 group by Remark) as b on a.name=b.Remark left join "
			+"(SELECT Remark, COUNT(*) as TotalNum from ActiveManagement where ActiveID='5' "
			+"and Remark <>'-1' and Status<>2 and Status<>-1 "
			+"group by Remark) as c on a.name=c.Remark ")
	List<Map> QueryWinningDetail();
}